Clarification regarding my Query

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

.NET Inside SQL Server (2005)

Clarification regarding my Query

Author

Topic

ambica_24
Starting Member

1 Post

Posted - 2008-11-22 : 06:19:51

hi,

im using sql server2005 for my .net application.

my requirement is:

im using a view to get the records in a grid.

here i have the fields in vwcalendar- activitydesc,time,date,unitsleft using these fields i have to display activitydesc,time and totalunits for one week i.e.

activitydesc,time,mondayunits,tuesdayunits,wednesdayunits,thursayunits,fridayunits,saturdayunits,sundayunits from vwcalendar

for this i have written a query as

select activitydesc,time,
(select max(UnitsLeft) as monunits from vwcalendar where date='2008-11-03') as monunits,
(select max(UnitsLeft) as tueunits from vwcalendar where date='2008-11-03') as tueunits,
(select max(UnitsLeft) as wedunits from vwcalendar where date='2008-11-03') as wedunits,
(select max(UnitsLeft) as thuunits from vwcalendar where date='2008-11-03') as thuunits,
(select max(UnitsLeft) as friunits from vwcalendar where date='2008-11-03') as friunits,
(select max(UnitsLeft) as satunits from vwcalendar where date='2008-11-03') as satunits,
(select max(UnitsLeft) as sununits from vwcalendar where date='2008-11-03') as sununits from vwcalendar

when i execute my application, it displays same unit for all reocrds to monunits column same as for other units like tuesday to sunday columns if there is a value to that dates.

i think the unitsleft values will be different for different records.

so i need a help from u regarding this whether im writing the query correct or not.

if not, can u please help me out to write the query.

please resolve my problem as early as possible.

Thanks,
Ambica

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-22 : 11:33:25

should be something like


SELECT activitydesc,
DATENAME(yyyy,DATEADD(wk,DATEDIFF(wk,0,date),0))+RIGHT('0'+DATENAME(wk,DATEADD(wk,DATEDIFF(wk,0,date),0)),2),
SUM(time) AS time,
SUM(CASE WHEN DATEPART(dw,date)=2 THEN unitsleft ELSE 0 END) AS mondayunits,
SUM(CASE WHEN DATEPART(dw,date)=3 THEN unitsleft ELSE 0 END) AS tuesdayunits,
SUM(CASE WHEN DATEPART(dw,date)=4 THEN unitsleft ELSE 0 END) AS wednesdayunits,
SUM(CASE WHEN DATEPART(dw,date)=5 THEN unitsleft ELSE 0 END) AS thursdayunits,
SUM(CASE WHEN DATEPART(dw,date)=6 THEN unitsleft ELSE 0 END) AS fridayunits,
SUM(CASE WHEN DATEPART(dw,date)=7 THEN unitsleft ELSE 0 END) AS saturdayunits,
SUM(CASE WHEN DATEPART(dw,date)=1 THEN unitsleft ELSE 0 END) AS sundayunits
FROM vwcalendar 
GROUP BY activitydesc,
DATENAME(yyyy,DATEADD(wk,DATEDIFF(wk,0,date),0))+RIGHT('0'+DATENAME(wk,DATEADD(wk,DATEDIFF(wk,0,date),0)),2)

i'm assuming your datefirst setting is 7, if not set it to 7 before running above query

SET DATEFIRST 7

you can check current setting using

SELECT @@DATEFIRST

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources