Wednesday, December 29, 2010

SMS Report for the packages advertised but not yet updated the DP

SELECT a.AdvertisementID,a.AdvertisementName,a.PresentTime as 'Advert Start Date',b.Name as 'Package Name',b.PackageID,count(b.PackageID) as 'Count of pending DP'
FROM v_Advertisement a
INNER JOIN v_Package b ON a.PackageID = b.PackageID
INNER JOIN v_DistributionPoint c ON b.PackageID = c.PackageID
INNER JOIN v_PackageStatusDistPointsSumm d ON c.PackageID = d.PackageID
WHERE d.state=1
GROUP BY a.AdvertisementID,a.AdvertisementName,a.PresentTime,b.PackageID,b.Name

Description: This report gives the count of each distribution points in which a specific advertised package is not updated.

3 Comments:

Blogger Atul Kr. said...

Thanks Jose,

there's something wrong with query as it outputs unexpected data. It's showing four times of actual DPs count. I am just looking into this and will confirm you soon.

Keep Querying... nice to see you.

December 29, 2010 at 4:34 AM  
Anonymous Anonymous said...

The above just retorne no records for me.
Not saying u script is wrong I may have lost something in the Cut and paste
See my request on MYitfprum Cjones464

December 31, 2010 at 5:25 AM  
Blogger Jaison Jose said...

@Atul...send you an email..check for multiple adverts for the same package...

@Cjones...replied you in Myitforum...

January 4, 2011 at 8:13 AM  

Post a Comment

Subscribe to Post Comments [Atom]

Links to this post:

Create a Link

<< Home