Solved

RE : SQL Server query 2008

Posted on 2013-07-01
6
281 Views
Last Modified: 2013-07-09
Hi,

I am facing challenge in creating SQL query in optimized way.

Find the attached sample data in excel sheet. I would like the date as per Output 1 and Output 2 sheet.

Do let me know if any question regarding the same.

Data requirement is that i have the consumption data now i want to place the cancellation data against each consumption. Attached the transactional data in excel.

Facing to
0
Comment
Question by:vikalgupta
  • 2
  • 2
  • 2
6 Comments
 
LVL 16

Expert Comment

by:EvilPostIt
ID: 39289746
Hi,

I think you have forgotten to attach the example data.
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39289804
and please post your existing SQL query (or include it in the spreadsheet)
0
 

Author Comment

by:vikalgupta
ID: 39290008
find the attached sheet.
rpt.xlsx
0
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
LVL 16

Expert Comment

by:EvilPostIt
ID: 39290128
Your not providing all the test data. You have not populated the shop master table. Although this does look like it will be a simple join.
0
 
LVL 48

Accepted Solution

by:
PortletPaul earned 500 total points
ID: 39292018
The following result is produced by either of the SQL options below. Please note the date format used is an arbitrary choice which you can amend.
MERCHANTID	VOUCHERNUMBER	CONSUMEDSHOPNAME	CONSUMEDSHOPCODE	CONSUMEDDATE	REQUESTJOBNO	CANCELLEDSHOPNAME	CANCELLEDSHOPCODE	CANCELLEDDATE
3		BV34145412	Karol Bagh		BR-GP001	2013-07-01 14:48:54	23		Green Park		BR-GP000	2013-07-01 14:49:48
3		BV55625541	Green Park		BR-GP000	2013-07-01 14:49:24	78		Rohini			BR-GP002	2013-07-01 14:49:33
3		BV55625541	Green Park		BR-GP000	2013-07-01 14:48:32	12		Karol Bagh		BR-GP001	2013-07-01 14:49:16
3		BV61731976	Rohini			BR-GP002	2013-07-01 14:49:03	34		(null)			(null)		(null)
3		BV74939357	Green Park		BR-GP000	2013-07-01 12:57:33	ADMIN_EVOUCHER	Green Park		BR-GP000	2013-07-01 12:58:15

Open in new window

There does not appear to be a tidy method within the source transactions to relate a cancellation to a consumption and to overcome this I have used the row_number() function to produce sequence numbers to tie cancellation to consumption. This works in the sample provided but suggest you test this on a broader set of data.

These 2 SQL options are functionally the same, they just differ in technique (using CTEs or using nested subquery)
-- using CTEs
;WITH
consume AS (
            SELECT
                  MERCHANTID
                , VoucherNumber
                , ShopMaster.SHOPNAME AS ConsumedShopName
                , ShopMaster.SHOPCODE AS ConsumedShopCode
                , REQUESTDATE         AS ConsumedDate
                , REQUESTJOBNO
                , row_number() over (partition BY VoucherNumber ORDER BY REQUESTDATE ASC) AS row_sequence
            FROM transtable
            INNER JOIN ShopMaster ON transtable.SHOPID = ShopMaster.SHOPID
            WHERE transtable.REQUESTTYPE = 'CONSUME'
            ),
cancel  AS (
            SELECT
                  VoucherNumber
                , ShopMaster.SHOPNAME  AS CancelledShopName
                , ShopMaster.SHOPCODE  AS CancelledShopCode
                , REQUESTDATE          AS CancelledDate
                , row_number() over (partition BY VoucherNumber ORDER BY REQUESTDATE ASC) AS row_sequence
            FROM transtable
            INNER JOIN ShopMaster ON transtable.SHOPID = ShopMaster.SHOPID
            WHERE transtable.REQUESTTYPE = 'CANCEL'
            )
SELECT
        consume.MERCHANTID
      , consume.VOUCHERNUMBER
      , consume.CONSUMEDSHOPNAME
      , consume.CONSUMEDSHOPCODE
      , convert(varchar, consume.CONSUMEDDATE ,120) AS CONSUMEDDATE
      , consume.REQUESTJOBNO
      , cancel.CANCELLEDSHOPNAME
      , cancel.CANCELLEDSHOPCODE
      , convert(varchar, cancel.CANCELLEDDATE ,120) AS CANCELLEDDATE
FROM consume
LEFT JOIN cancel ON consume.VoucherNumber = cancel.VoucherNumber
                AND consume.row_sequence = cancel.row_sequence
ORDER BY
        consume.VoucherNumber
      , CONSUMEDDATE DESC
;

-- using nested subqueries
SELECT
        consume.MERCHANTID
      , consume.VOUCHERNUMBER
      , consume.CONSUMEDSHOPNAME
      , consume.CONSUMEDSHOPCODE
      , convert(varchar, consume.CONSUMEDDATE ,120) AS CONSUMEDDATE
      , consume.REQUESTJOBNO
      , cancel.CANCELLEDSHOPNAME
      , cancel.CANCELLEDSHOPCODE
      , convert(varchar, cancel.CANCELLEDDATE ,120) AS CANCELLEDDATE
FROM (
            SELECT
                  MERCHANTID
                , VoucherNumber
                , ShopMaster.SHOPNAME AS ConsumedShopName
                , ShopMaster.SHOPCODE AS ConsumedShopCode
                , REQUESTDATE         AS ConsumedDate
                , REQUESTJOBNO
                , row_number() over (partition BY VoucherNumber ORDER BY REQUESTDATE ASC) AS row_sequence
            FROM transtable
            INNER JOIN ShopMaster ON transtable.SHOPID = ShopMaster.SHOPID
            WHERE transtable.REQUESTTYPE = 'CONSUME'
     ) AS consume
LEFT JOIN (
            SELECT
                  VoucherNumber
                , ShopMaster.SHOPNAME  AS CancelledShopName
                , ShopMaster.SHOPCODE  AS CancelledShopCode
                , REQUESTDATE          AS CancelledDate
                , row_number() over (partition BY VoucherNumber ORDER BY REQUESTDATE ASC) AS row_sequence
            FROM transtable
            INNER JOIN ShopMaster ON transtable.SHOPID = ShopMaster.SHOPID
            WHERE transtable.REQUESTTYPE = 'CANCEL'
          ) AS cancel ON consume.VoucherNumber = cancel.VoucherNumber
                     AND consume.row_sequence = cancel.row_sequence
ORDER BY
        consume.VoucherNumber
      , CONSUMEDDATE DESC
;

Open in new window

also note you didn't specify the actual table names, and I removed spaces from any column labels in the spreadsheet.

these queries and the assumed shopmaster table may be seen in operation at:
http://sqlfiddle.com/#!3/84505/3
0
 

Author Closing Comment

by:vikalgupta
ID: 39313199
Thanks
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…
I designed this idea while studying technology in the classroom.  This is a semester long project.  Students are asked to take photographs on a specific topic which they find meaningful, it can be a place or situation such as travel or homelessness.…

914 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now